Updateable Join Views

High volume UPDATE statements with SET sub-queries can be one of hardest SQLs to tune. Consider the following:

UPDATE my_table a
SET col1 = (
           SELECT col1
           FROM my_temp_table
           WHERE key = a.key)
WHERE col3 = 'BLORT'

This SQL will probably perform a full table scan on my_table (not such a problem) and then for every row returned, it will perform an indexed SELECT on my_temp_table to retrieve the new value for col1. This style of execution is synonymous with a Nested Loops join. As discussed elsewhere in this guide, Nested Loops joins are appropriate for small data volumes; large data volumes are better served with Sort-merge and Hash joins.

What is required is the ability to join the two tables with a Hash join and update at the same time. Well it is possible - with an Updateable Join View.

UPDATE (
    SELECT a.col3, a.col1 AS old_value, b.col1 AS new_value
    FROM   my_table a
    ,      my_temp_table b
    WHERE  a.col1 = b.col1)
SET old_value = new_value
WHERE col3 = 'BLORT'

The example above shows an update of a dynamic or nested view. It is also possible to update an explicit view that is declared on the database.

Clearly the view cannot contain DISTINCT, GROUP BY, CONNECT BY, aggregate functions, UNION, INTERSECT, or MINUS clauses: simple joins are all that is allowed. There is one other restriction: the view must be key preserved. This means that the join must meet the following restrictions:

• The join criteria must be equals (=) joins.
• Tables other than the table that is to be updated must be joined on a primary or unique key.

If these conditions are violated, then Oracle cannot guarantee that the view will return one row only for each row in the base table. If two or more rows in the secondary table mapped to each row in the base table, then the update would be ambiguous. An attempt to update a non-key-preserved view will result in an Oracle error.

If you are performing a high volume update, but cannot use an Updateable Join View because it would not be key-preserved or would require GROUP BY etc., then try using an intermediate temporary table. Create a temporary table with the same primary key as the base table, and use a SELECT statement to insert the keys and the updated column values. Then use an Updateable Join View to update the base table. Even though this seems like more work, it is still a lot quicker that the traditional Nested Loop style update.

Alternatively, if you are using Oracle 10g or later, use the MERGE statement without a WHEN NOT MATCHED clause

For the very courageous only, there is a way to update a non-key-preserved view. Oracle uses an undocumented hint BYPASS_UJVC (Bypass Updateable Join View Constraints) internally to refresh materialized views. Note that if the join returns multiple rows for each row of the table to be updated, then you will get a non-deterministic result. Also note that since this hint is undocumented, Oracle could remove it without notice, leaving your code invalid and useless. I strongly suggest you use BYPASS_UJVC for once-only updates, and never for permanent production code.